﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using Power.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Power.Common
{
    /// <summary>
    /// author:TT
    /// time:2021-08-30 23:14:37
    /// desc:ExcelHelper
    /// </summary>
    public class ExcelHelper
    {
        
        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static (DataTable DataTable,List<PicturesInfo> PicList) ReadExcel(string filePath)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            DataTable dataTable = null;
            DataColumn column = null;
            DataRow dataRow = null;
            int startRow = 0;
            IRow row = null;
            ICell cell = null;
            using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                if (filePath.ToLower().EndsWith(".xlsx"))
                    workbook = new XSSFWorkbook(fileStream);
                // 2003版本  
                else if (filePath.ToLower().EndsWith(".xls"))
                    workbook = new HSSFWorkbook(fileStream);
                if (workbook == null) return (null,null);
                sheet = workbook.GetSheetAt(0);
                if (sheet == null) return (null, null);

                dataTable = new DataTable();
                int rowCount = sheet.LastRowNum;//总行数
                IRow firstRow = sheet.GetRow(0);//第一行  
                startRow = 1;//如果第一行是列名，则从第二行开始读取 
                List<PicturesInfo> picInfoList = null;
                if (firstRow.GetCell(0).StringCellValue.Contains("成果表")) {
                    startRow = 4;
                    firstRow = sheet.GetRow(1);
                    picInfoList = sheet.GetAllPictureInfos();
                }
                int cellCount = firstRow.LastCellNum;//列数
                column = new DataColumn("RowNum");
                dataTable.Columns.Add(column);
                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                {
                    cell = firstRow.GetCell(i);
                    if (cell != null && cell.StringCellValue != null)
                    {
                        column = new DataColumn(cell.StringCellValue);
                        dataTable.Columns.Add(column);
                    }
                }
                for (int i = startRow; i <= rowCount; ++i)
                {
                    row = sheet.GetRow(i);
                    if (row == null) continue;
                    dataRow = dataTable.NewRow();
                    dataRow[0] = row.RowNum;
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        cell = row.GetCell(j);
                        if (cell == null)
                        {
                            dataRow[j + 1] = "";
                        }
                        else
                        {
                            //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)  
                            switch (cell.CellType)
                            {
                                case CellType.Blank:
                                    dataRow[j + 1] = "";
                                    break;
                                case CellType.Numeric:
                                    short format = cell.CellStyle.DataFormat;
                                    //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理  
                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                        dataRow[j + 1] = cell.DateCellValue;
                                    else
                                        dataRow[j + 1] = cell.NumericCellValue;
                                    break;
                                case CellType.String:
                                    dataRow[j + 1] = cell.StringCellValue;
                                    break;
                            }
                        }
                    }
                    if (startRow == 4) {
                        int index = dataTable.Columns.IndexOf("是否删除");
                        if (index > 0 && Convert.ToString(dataRow[index]) == "是") {
                            //移除对应图片
                            if (picInfoList != null && picInfoList.Count > 0) {
                                picInfoList.RemoveAt(dataTable.Rows.Count);
                                continue;
                            }
                        }
                    }
                    dataTable.Rows.Add(dataRow);
                }
                return (dataTable,picInfoList);
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="imageNameDel"></param>
        /// <returns></returns>
        public static (bool Success, string Message) DeleteExcelRow(string filePath,string imgName,int deleteNum = -1)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            int startRow = 0;
            bool flag = false; //标记是否删除成功
            IRow row = null;
            ICell cell = null;
            using (FileStream streamRead = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Read))
            {
                if (filePath.ToLower().EndsWith(".xlsx"))
                    workbook = new XSSFWorkbook(streamRead);
                // 2003版本  
                else if (filePath.ToLower().EndsWith(".xls"))
                    workbook = new HSSFWorkbook(streamRead);
            }
            if (workbook == null) return (false,"未能加载Excel");
            sheet = workbook.GetSheetAt(0);
            if (sheet == null) return (false, "未能加载Sheet");

            int rowCount = sheet.LastRowNum;//总行数
            IRow firstRow = sheet.GetRow(0);//第一行  
            int cellCount = firstRow.LastCellNum;//列数
            if (deleteNum == -1)
            {
                startRow = 1;//如果第一行是列名，则从第二行开始读取
                int cellNum = firstRow.FirstOrDefault(a => a.StringCellValue == "检测图片")?.ColumnIndex ?? -1;
                if (cellNum == -1) return (false, "无法正确找到检测图片列");
                for (int i = startRow; i <= rowCount; ++i)
                {
                    string imageName = "";
                    row = sheet.GetRow(i);
                    if (row == null) continue;
                    cell = row.GetCell(cellNum);
                    if (cell == null) continue;
                    switch (cell.CellType)
                    {
                        case CellType.Numeric:
                            short format = cell.CellStyle.DataFormat;
                            imageName = Convert.ToString(cell.NumericCellValue);
                            break;
                        case CellType.String:
                            imageName = cell.StringCellValue;
                            break;
                    }
                    if (imgName == imageName)
                    {
                        sheet.ShiftRows(i + 1, sheet.LastRowNum, -1, false, false);
                        flag = true;
                        break;
                    }
                }
            }
            else {
                //创建单元格样式
                ICellStyle cellStyle = workbook.CreateCellStyle();
                //设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");
                cellStyle.BorderBottom = BorderStyle.Thin;    //下边框线
                cellStyle.BorderLeft = BorderStyle.Thin;      //左边框线
                cellStyle.BorderRight = BorderStyle.Thin;     //右边框线
                cellStyle.BorderTop = BorderStyle.Thin;       //上边框线
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                cellStyle.Alignment = HorizontalAlignment.Center;
                firstRow = sheet.GetRow(1);//第一行  
                cellCount = firstRow.LastCellNum;
                int? index = firstRow.FirstOrDefault(a => a.StringCellValue == "是否删除")?.ColumnIndex;
                if (index == null)
                {
                    SetCellValue(firstRow, cellCount, "是否删除", cellStyle);
                    SetCellValue(sheet.GetRow(2), cellCount, "", cellStyle);
                    SetCellValue(sheet.GetRow(3), cellCount, "", cellStyle);
                    CellRangeAddress region = new CellRangeAddress(1, 3, cellCount, cellCount);
                    sheet.AddMergedRegion(region);
                    index = cellCount;
                }
                row = sheet.GetRow(deleteNum);
                SetCellValue(row,index.Value, "是", cellStyle);
                flag = true;
            }
            if (!flag) return (false, $"未能找到检测图片为{imgName}的所在行");
            using (FileStream streamWrite = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(streamWrite);
            }
            if (workbook != null)
            {
                workbook.Close();
            }
            return (true,"");
        }

        /// <summary>
        /// 写入ai识别结果
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        public static (bool Success, string Message) WriteExcelRow(string filePath, string imgName, DefectEntity model,string postion,int writeIndex = -1)
        {
            IWorkbook workbook = null;
            ISheet sheet = null;
            int startRow = 0;
            bool flag = false; //标记是否删除成功
            IRow row = null;
            ICell cell = null;
            string[] cellNameArr = new string[5] { "缺陷代码","缺陷等级","检测描述","AI缺陷环向", "缺陷坐标" };
            List<int> cellNumList = new List<int>();
            using (FileStream streamRead = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Read))
            {
                if (filePath.ToLower().EndsWith(".xlsx"))
                    workbook = new XSSFWorkbook(streamRead);
                // 2003版本  
                else if (filePath.ToLower().EndsWith(".xls"))
                    workbook = new HSSFWorkbook(streamRead);
            }
            if (workbook == null) return (false, "未能加载Excel");
            sheet = workbook.GetSheetAt(0);
            if (sheet == null) return (false, "未能加载Sheet");

            //创建单元格样式
            ICellStyle cellStyle = workbook.CreateCellStyle();
            //设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");
            cellStyle.BorderBottom = BorderStyle.Thin;    //下边框线
            cellStyle.BorderLeft = BorderStyle.Thin;      //左边框线
            cellStyle.BorderRight = BorderStyle.Thin;     //右边框线
            cellStyle.BorderTop = BorderStyle.Thin;       //上边框线
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Center;

            int rowCount = sheet.LastRowNum;//总行数
            IRow headRow = sheet.GetRow(0);//第一行  
            if (writeIndex != -1)
            {
                startRow = 4;
                headRow = sheet.GetRow(1);
            }
            else {
                startRow = 1;//如果第一行是列名，则从第二行开始读取
            }
            int cellCount = headRow.LastCellNum;//列数
            foreach (var item in cellNameArr)
            {
                var itemlower = item.ToLower();
                int? index = headRow.FirstOrDefault(a => a.StringCellValue == item || a.StringCellValue == itemlower)?.ColumnIndex;
                if (index == null)
                {
                    SetCellValue(headRow, cellCount, item, cellStyle);
                    if (startRow == 4)
                    {
                        SetCellValue(sheet.GetRow(2), cellCount, "", cellStyle);
                        SetCellValue(sheet.GetRow(3), cellCount, "", cellStyle);
                        CellRangeAddress region = new CellRangeAddress(1, 3, cellCount, cellCount);
                        sheet.AddMergedRegion(region);
                    }
                    cellNumList.Add(cellCount++);
                }
                else {
                    cellNumList.Add(index.Value);
                }
            }
            if (writeIndex == -1)
            {
                int cellNum = headRow.FirstOrDefault(a => a.StringCellValue == "检测图片")?.ColumnIndex ?? -1;
                if (cellNum == -1) return (false, "无法正确找到检测图片列");
                for (int i = startRow; i <= rowCount; ++i)
                {
                    string imageName = "";
                    row = sheet.GetRow(i);
                    if (row == null) continue;
                    cell = row.GetCell(cellNum);
                    if (cell == null) continue;
                    switch (cell.CellType)
                    {
                        case CellType.Numeric:
                            short format = cell.CellStyle.DataFormat;
                            imageName = Convert.ToString(cell.NumericCellValue);
                            break;
                        case CellType.String:
                            imageName = cell.StringCellValue;
                            break;
                    }
                    if (imgName == imageName)
                    {
                        //赋值对应列数据
                        SetCellValue(row, cellNumList[0], model.Code, cellStyle);
                        SetCellValue(row, cellNumList[1], Convert.ToString(model.Level + 1), cellStyle);
                        SetCellValue(row, cellNumList[2], model.Describe, cellStyle);
                        SetCellValue(row, cellNumList[3], model.CircleDirection, cellStyle);
                        SetCellValue(row, cellNumList[4], postion, cellStyle);
                        flag = true;
                        break;
                    }
                }
            }
            else {
                row = sheet.GetRow(writeIndex);
                //赋值对应列数据
                SetCellValue(row, cellNumList[0], model.Code, cellStyle);
                SetCellValue(row, cellNumList[1], Convert.ToString(model.Level + 1), cellStyle);
                SetCellValue(row, cellNumList[2], model.Describe, cellStyle);
                SetCellValue(row, cellNumList[3], model.CircleDirection, cellStyle);
                SetCellValue(row, cellNumList[4], postion, cellStyle);
                flag = true;
            }
            if (!flag) return (false, $"未能找到检测图片为{imgName}的所在行");
            using (FileStream streamWrite = new FileStream(filePath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(streamWrite);
            }
            if (workbook != null)
            {
                workbook.Close();
            }
            return (flag,"");
        }

        /// <summary>
        /// 单元格添加值
        /// </summary>
        /// <param name="row"></param>
        /// <param name="index"></param>
        /// <param name="value"></param>
        /// <param name="cellStyle"></param>
        private static void SetCellValue(IRow row,int index,string value,ICellStyle cellStyle) {
            var cell = (row.GetCell(index) ?? row.CreateCell(index));
            cell.SetCellValue(value);
            cell.CellStyle = cellStyle;
        }
    }
    public static class NpoiExtend
    {
        public static List<PicturesInfo> GetAllPictureInfos(this ISheet sheet)
        {
            return sheet.GetAllPictureInfos(null, null, null, null);
        }

        public static List<PicturesInfo> GetAllPictureInfos(this ISheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal = true)
        {
            if (sheet is HSSFSheet)
            {
                return GetAllPictureInfos((HSSFSheet)sheet, minRow, maxRow, minCol, maxCol, onlyInternal);
            }
            else if (sheet is XSSFSheet)
            {
                return GetAllPictureInfos((XSSFSheet)sheet, minRow, maxRow, minCol, maxCol, onlyInternal);
            }
            else
            {
                throw new Exception("未处理类型，没有为该类型添加：GetAllPicturesInfos()扩展方法！");
            }
        }

        private static List<PicturesInfo> GetAllPictureInfos(HSSFSheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal)
        {
            List<PicturesInfo> picturesInfoList = new List<PicturesInfo>();

            var shapeContainer = sheet.DrawingPatriarch as HSSFShapeContainer;
            if (null != shapeContainer)
            {
                var shapeList = shapeContainer.Children;
                foreach (var shape in shapeList)
                {
                    if (shape is HSSFPicture && shape.Anchor is HSSFClientAnchor)
                    {
                        var picture = (HSSFPicture)shape;
                        var anchor = (HSSFClientAnchor)shape.Anchor;

                        if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal))
                        {
                            picturesInfoList.Add(new PicturesInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data));
                        }
                    }
                }
            }

            return picturesInfoList;
        }

        private static List<PicturesInfo> GetAllPictureInfos(XSSFSheet sheet, int? minRow, int? maxRow, int? minCol, int? maxCol, bool onlyInternal)
        {
            List<PicturesInfo> picturesInfoList = new List<PicturesInfo>();

            var documentPartList = sheet.GetRelations();
            foreach (var documentPart in documentPartList)
            {
                if (documentPart is XSSFDrawing)
                {
                    var drawing = (XSSFDrawing)documentPart;
                    var shapeList = drawing.GetShapes();
                    foreach (var shape in shapeList)
                    {
                        if (shape is XSSFPicture)
                        {
                            var picture = (XSSFPicture)shape;
                            var anchor = picture.GetPreferredSize();

                            if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal))
                            {
                                picturesInfoList.Add(new PicturesInfo(anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, picture.PictureData.Data));
                            }
                        }
                    }
                }
            }

            return picturesInfoList;
        }

        private static bool IsInternalOrIntersect(int? rangeMinRow, int? rangeMaxRow, int? rangeMinCol, int? rangeMaxCol,
            int pictureMinRow, int pictureMaxRow, int pictureMinCol, int pictureMaxCol, bool onlyInternal)
        {
            int _rangeMinRow = rangeMinRow ?? pictureMinRow;
            int _rangeMaxRow = rangeMaxRow ?? pictureMaxRow;
            int _rangeMinCol = rangeMinCol ?? pictureMinCol;
            int _rangeMaxCol = rangeMaxCol ?? pictureMaxCol;

            if (onlyInternal)
            {
                return (_rangeMinRow <= pictureMinRow && _rangeMaxRow >= pictureMaxRow &&
                        _rangeMinCol <= pictureMinCol && _rangeMaxCol >= pictureMaxCol);
            }
            else
            {
                return ((Math.Abs(_rangeMaxRow - _rangeMinRow) + Math.Abs(pictureMaxRow - pictureMinRow) >= Math.Abs(_rangeMaxRow + _rangeMinRow - pictureMaxRow - pictureMinRow)) &&
                (Math.Abs(_rangeMaxCol - _rangeMinCol) + Math.Abs(pictureMaxCol - pictureMinCol) >= Math.Abs(_rangeMaxCol + _rangeMinCol - pictureMaxCol - pictureMinCol)));
            }
        }
    }
    public class PicturesInfo
    {
        public int MinRow { get; set; }
        public int MaxRow { get; set; }
        public int MinCol { get; set; }
        public int MaxCol { get; set; }
        public Byte[] PictureData { get; private set; }

        public PicturesInfo(int minRow, int maxRow, int minCol, int maxCol, Byte[] pictureData)
        {
            this.MinRow = minRow;
            this.MaxRow = maxRow;
            this.MinCol = minCol;
            this.MaxCol = maxCol;
            this.PictureData = pictureData;
        }
    }
}
